/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.prashant.sqlduniya.dbcp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.openide.util.lookup.ServiceProvider;
import org.prashant.sqlduniya.sqlmanager.*;
import org.prashant.sqlduniya.sqlmanager.error.SQLDuniyaResultException;

/**
 *
 * @author prashant
 */
@ServiceProvider(service = DBConnectionManager.class)
public class SqlConnectionProvider implements DBConnectionManager {

    private static final Logger logger = Logger.getLogger(SqlConnectionProvider.class.getName());
    private DBConnectionInfo activeConnection;

    @Override
    public String ping(DBConnectionInfo info) {
        String msg = "";

        try {
            Connection connection = null;
            if ((connection = connect(info,10)) != null) {
                //close the obtained connection
                release(connection);
                logger.log(Level.INFO, "It works.....{0}", info.getName());
                msg = "Success";
            }
        } catch (SQLException ex) {
            msg = ex.getMessage();
            logger.log(Level.FINE, "Failed connecting to.....[{0}] Message: {1}", new Object[]{info.getServer(), msg});
        }
        return msg;
    }

    @Override
    public List<DBConnectionInfo> getConnections() {
        List<DBConnectionInfo> list = new ArrayList<DBConnectionInfo>();
        
        DBConnectionInfo info = null;        
        
        info = new DBConnectionInfo();
        info.setName("New Connection");
        Credential c = new Credential(System.getProperty("user.name"),"");
        info.setCredential(c);
        list.add(info);
        
        info = new DBConnectionInfo();
        info.setName("Local DB");
        info.setServer("127.0.0.1");
        info.setPort(3306);
        c = new Credential("root", "adminadmin");
        info.setCredential(c);

        list.add(info);

        c = new Credential("appli", "attano@123");
        info = new DBConnectionInfo();
        info.setName("QC Server");
        info.setServer("172.19.10.41");
        info.setPort(3306);
        info.setCredential(c);
        list.add(info);
        return list;
    }

    @Override
    public DBNode loadDB(DBConnectionInfo info) {
        this.activeConnection = info;
        DBNode node = new DBNode();
        try {
            Connection conn = connect(info,-1);
            //node.populateTables(DBInfoUtil.getTables(conn));            
            node.setSchemas(DBInfoUtil.getSchemas(conn, info.getCatalog()));
            release(conn);
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
        }
        return node;
    }

    @Override
    public List<String> loadTables(DBConnectionInfo info, String catalog) {
        this.activeConnection = info;
        List<String> tables = new ArrayList<String>();
        try {
            Connection conn = connect(info,-1);
            //node.populateTables(DBInfoUtil.getTables(conn));
            tables = DBInfoUtil.getTables(conn, catalog);
            logger.log(Level.INFO, "Found {0} tables for {1}", new Object[]{tables.size(), catalog});
            release(conn);
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
        }
        return tables;
    }

    private Connection connect(DBConnectionInfo info,int timeout) throws SQLException {
        if(timeout > 0)
            DriverManager.setLoginTimeout(timeout);
        this.activeConnection = info;
        String url = "jdbc:mysql://" + info.getServer() + ":" + info.getPort();
        if (info.getCatalog() != null) {
            url = url + "/" + info.getCatalog();
        }
        url = url + "?zeroDateTimeBehavior=convertToNull";
        logger.log(Level.INFO, "Connecting to.....{0}", url);
        return DriverManager.getConnection(url,
                info.getCredential().getUserName(),
                info.getCredential().getPassword());
    }

    private void release(Connection con) {
        if (con != null) {
            try {
                con.close();
            } catch (SQLException ex) {
            }
        }
    }

    @Override
    public SQLResult loadTable(TableBean table, int offset, int limit)
            throws SQLDuniyaResultException {
        SQLResult result = new SQLResult();
        try {
            Connection conn = connect(activeConnection,-1);
            result = DBInfoUtil.getTableData(conn, table, offset, limit);

            release(conn);
        } catch (SQLException ex) {
            throw new SQLDuniyaResultException(ex.getMessage());
        }
        return result;
    }

    @Override
    public SQLResult loadResult(String query) throws SQLDuniyaResultException {
        SQLResult result = new SQLResult();
        try {
            Connection conn = connect(activeConnection,-1);
            result = DBInfoUtil.getResultData(conn, query);

            release(conn);
        } catch (SQLException ex) {
            throw new SQLDuniyaResultException(ex.getMessage());
        }
        return result;
    }

    @Override
    public DBConnectionInfo getActiveConnection() {
        return activeConnection;
    }
}
